﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.Linq;
using System.Text;
using System.Data.SqlClient;
using System.ComponentModel;
using System.Data;
using System.Windows.Forms;


namespace VisionarySystems.Library
{
	public class DatabaseUtils
	{
		#region private members
		private string dataSource = string.Empty;
		#endregion

		#region Ctor
		public DatabaseUtils(string DataSource)
		{
			dataSource = DataSource;
		}
		#endregion

		#region Public methods

		public bool CheckDatabaseExists(string DatabaseName)
		{
			bool Result = false;
			string SqlCheckTable = @"
				select Count(Name) as [Count] from sys.databases where 
				name = @DatabaseName";

			SqlConnection Conn = GetConnection ("Master") ;
			
			try
			{
				Conn.Open();
				SqlCommand cmdCheckDatabase = Conn.CreateCommand();
				cmdCheckDatabase.CommandText = SqlCheckTable;
				cmdCheckDatabase.Parameters.Add("@DatabaseName", System.Data.SqlDbType.VarChar, 100);
				cmdCheckDatabase.Parameters["@databaseName"].Value = DatabaseName;
				SqlDataReader rdr = cmdCheckDatabase.ExecuteReader();
				if (rdr.Read())
					Result = Int32.Parse(rdr["Count"].ToString()) > 0;
			}
			finally
			{
				if (Conn.State == ConnectionState.Open)
					Conn.Close();
			}
			return Result;
		}

		public SqlConnection GetConnection(string DatabaseName)
		{
			SqlConnection conn = new SqlConnection(@"
				data source = .\" + dataSource + ";" +
				"integrated security = true;" +
				"database = " + DatabaseName
			);
			return conn;
		}

		public DataContext GetDataContext(string DatabaseName)
		{
			DataContext db = new DataContext(@"
				data source = .\" + dataSource + ";" +
				"integrated security = true;" +
				"database = " + DatabaseName
			);
			return db;
		}

		public bool CheckTableExists(SqlConnection Conn, string TableName)
		{
			bool Result = false;
			string SqlCheckTable = @"
				select count(name) as [Count] from sysobjects where 
				xtype ='U' and name = @TableName";
			try
			{
				if (Conn.State != ConnectionState.Open)
					Conn.Open();
				SqlCommand cmdCheckTable = Conn.CreateCommand();
				cmdCheckTable.CommandText = SqlCheckTable;
				cmdCheckTable.Parameters.Add("@TableName", System.Data.SqlDbType.VarChar, 100);
				cmdCheckTable.Parameters["@TableName"].Value = TableName;
				SqlDataReader rdr = cmdCheckTable.ExecuteReader();
				if (rdr.Read())
					Result = Int32.Parse(rdr["Count"].ToString()) > 0;
			}
			finally
			{
				if (Conn.State == ConnectionState.Open)
					Conn.Close();
			}
			return Result;
		}

		public bool CheckRowExists(SqlConnection Conn, SqlTransaction SqlTrans, string TableName, string ColoumnName, DateTime Value)
		{
			bool Result = false;
			string SqlCheckRow = @"
				select count(*) as [Count] from " + TableName + " where " +
				ColoumnName + "= @Value";
			SqlDataReader rdr = null;
			try
			{
				if (Conn.State != ConnectionState.Open)
					Conn.Open();
				SqlCommand cmdCheckTable = Conn.CreateCommand();
				if (SqlTrans != null)
					cmdCheckTable.Transaction = SqlTrans;
				cmdCheckTable.CommandText = SqlCheckRow;
				cmdCheckTable.Parameters.Add("@Value", System.Data.SqlDbType.Date);
				cmdCheckTable.Parameters["@Value"].Value = Value;
				rdr = cmdCheckTable.ExecuteReader();
				if (rdr.Read())
					Result = Int32.Parse(rdr["Count"].ToString()) > 0;

			}
			finally
			{
				rdr.Close();
			}
			return Result;
		}

		public void CreateDatabase(string DatabaseName)
		{
			//String str;
			//SqlConnection myConn = new SqlConnection("Server=localhost;Integrated security=SSPI;database=master");
			SqlConnection Conn = GetConnection ("Master") ;

			/*
				str = "CREATE DATABASE MyDatabase ON PRIMARY " +
				   "(NAME = MyDatabase_Data, " +
				   "FILENAME = 'C:\\MyDatabaseData.mdf', " +
				   "SIZE = 2MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " +
				   "LOG ON (NAME = MyDatabase_Log, " +
				   "FILENAME = 'C:\\MyDatabaseLog.ldf', " +
				   "SIZE = 1MB, " +
				   "MAXSIZE = 5MB, " +
				   "FILEGROWTH = 10%)";
			*/

			SqlCommand myCommand = new SqlCommand("Create Database " + DatabaseName, Conn);
			try
			{
				Conn.Open();
				myCommand.ExecuteNonQuery();
				MessageBox.Show("DataBase is Created Successfully", DatabaseName, MessageBoxButtons.OK, MessageBoxIcon.Information);
			}
			catch (System.Exception ex)
			{
				MessageBox.Show(ex.ToString(), "Fejl ved oprettelse af database", MessageBoxButtons.OK, MessageBoxIcon.Error);
			}
			finally
			{
				if (Conn.State == ConnectionState.Open)
					Conn.Close();
			}
		}

		public void CreateStoredProcedure(SqlConnection Conn, string Procedurename, string Content)
		{
			string SqlCreateStoredProcedure = @"
			CREATE procedure " + Procedurename +
			" as " + Content;

			try
			{
				if (Conn.State != ConnectionState.Open)
					Conn.Open();
				SqlCommand cmdCreateStoredProcedure = Conn.CreateCommand();
				cmdCreateStoredProcedure.CommandText = SqlCreateStoredProcedure;
				cmdCreateStoredProcedure.ExecuteNonQuery();
			}
			finally
			{
				//                if (Conn.State == ConnectionState.Open)
				//                    Conn.Close();
			}
		}

		public void DropDatabase(string DatabaseName)
		{
			SqlConnection Conn = GetConnection("Master");

			SqlCommand myCommand = new SqlCommand("Drop Database " + DatabaseName, Conn);
			try
			{
				Conn.Open();
				myCommand.ExecuteNonQuery();
			}
			catch (System.Exception ex)
			{
				MessageBox.Show(ex.ToString(), "Fejl ved sletning af database", MessageBoxButtons.OK, MessageBoxIcon.Error);
			}
			finally
			{
			}
		}

		public void CreateTable(SqlConnection Conn, string SQLCreateString)
		{
/*            string SqlCreateRegDaysTable = @"
			CREATE TABLE RegDays (
			RegDate Date not null Primary Key
			)";

			string SqlCreateTimeSlotTable = @"
			CREATE TABLE TimeSlots (
			Idx int IDENTITY(1000, 1) not null Primary Key
			, Start DateTime not null
			, Stop DateTime not null
			, RegDate Date not null 
				References RegDays
					On Update Cascade
						On delete Cascade)";

			string SqlCreateSystemInfoTable = @"
			CREATE TABLE SystemInfo (
			VersionNo int not null 
			)";*/
			try
			{
				if (Conn.State != ConnectionState.Open)
					Conn.Open();
				SqlCommand cmdCreateTable = Conn.CreateCommand();
				cmdCreateTable.CommandText = SQLCreateString;
				cmdCreateTable.ExecuteNonQuery();
			}
			catch (SqlException se)
			{
				MessageBox.Show("fejl ved oprettelse af tabel :" + SQLCreateString + ": " + se.Message);
			}
			finally
			{
				//                if (Conn.State == ConnectionState.Open)
				//                    Conn.Close();
			}
		}
		#endregion
	}
}
